package com.fenrirtec.aepp.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.condition.TenderCondition;
import com.fenrirtec.aepp.common.dao.InformationDao;
import com.fenrirtec.aepp.common.model.TenderInformation;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;

public class InformationDao_JDBCImpl implements InformationDao{
	private static final Logger logger = LoggerFactory.getLogger(InformationDao_JDBCImpl.class);

    @Override
    public Integer countByCnd(TenderCondition condition) {
        if (logger.isInfoEnabled()) {
            logger.info("[InformationDao_JDBCImpl#countByCnd] start.");
        }

        PreparedStatement statement = null;
        
        int count = 0;
        
        try {

            StringBuilder sql = new StringBuilder()
                .append("select \n")
                .append("  count(tender_id) as count \n")
                .append("from \n")
                .append("  t_tender_information \n")
                .append("where \n")
                .append("  delete_flag = false \n");
            
            if (condition != null) {
                if (StringUtils.isNotEmpty(condition.getTenderTitle())) {
                    sql.append("and \n");
                    sql.append("  tender_title like ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollStartDateFrom())) {
                    sql.append("and \n");
                    sql.append("  enroll_start_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollStartDateTo())) {
                    sql.append("and \n");
                    sql.append("  enroll_start_date <= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollEndDateFrom())) {
                    sql.append("and \n");
                    sql.append("  enroll_end_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollEndDateTo())) {
                    sql.append("and \n");
                    sql.append("  enroll_end_date <= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getTenderEndDateFrom())) {
                    sql.append("and \n");
                    sql.append("  tender_end_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getTenderEndDateTo())) {
                    sql.append("and \n");
                    sql.append("  tender_end_date <= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getPublishResultDateFrom())) {
                    sql.append("and \n");
                    sql.append("  publish_result_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getPublishResultDateTo())) {
                    sql.append("and \n");
                    sql.append("  publish_result_date <= ? \n");
                }
                if (condition.getTenderPattern() != null && condition.getTenderPattern().intValue() > 0) {
                    sql.append("and \n");
                    sql.append("  tender_pattern = ? \n");
                }
                if (condition.getStatus() != null && condition.getStatus().length > 0) {
                    sql.append("and \n");
                    String strStatus = "";
                    for (int i = 0; i < condition.getStatus().length; i++) {
                        if (i == 0) {
                            strStatus = "?";
                        } else {
                            strStatus += ", ?";
                        }
                    }
                    sql.append("  status in (" + strStatus + ") \n");
                }
                if (condition.getTimeLimit() != null && condition.getTimeLimit()==0) {
                    sql.append("and \n");
                    sql.append("  create_date >= DATE_FORMAT(NOW(),'%Y') \n");
                }
                if (condition.getTimeLimit() != null && condition.getTimeLimit()==1) {
                    sql.append("and \n");
                    sql.append("  create_date >= DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'), INTERVAL 3 MONTH) \n");
                }
                if (condition.getTimeLimit() != null && condition.getTimeLimit()==2) {
                    sql.append("and \n");
                    sql.append("  create_date < DATE_FORMAT(NOW(),'%Y') \n");
                }
            }
            
            if (logger.isTraceEnabled()) {
                logger.trace("[InformationDao_JDBCImpl#countByCnd] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            
            if (condition != null) {
                int index = 0;
                if (StringUtils.isNotEmpty(condition.getTenderTitle())) {
                    statement.setString(++index, "%" + condition.getTenderTitle() + "%");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollStartDateFrom())) {
                    statement.setString(++index, condition.getEnrollStartDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getEnrollStartDateTo())) {
                    statement.setString(++index, condition.getEnrollStartDateTo());
                }
                if (StringUtils.isNotEmpty(condition.getEnrollEndDateFrom())) {
                    statement.setString(++index, condition.getEnrollEndDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getEnrollEndDateTo())) {
                    statement.setString(++index, condition.getEnrollEndDateTo());
                }
                if (StringUtils.isNotEmpty(condition.getTenderEndDateFrom())) {
                    statement.setString(++index, condition.getTenderEndDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getTenderEndDateTo())) {
                    statement.setString(++index, condition.getTenderEndDateTo());
                }
                if (StringUtils.isNotEmpty(condition.getPublishResultDateFrom())) {
                    statement.setString(++index, condition.getPublishResultDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getPublishResultDateTo())) {
                    statement.setString(++index, condition.getPublishResultDateTo());
                }
                if (condition.getTenderPattern() != null && condition.getTenderPattern().intValue() > 0) {
                    statement.setInt(++index, condition.getTenderPattern());
                }
                if (condition.getStatus() != null && condition.getStatus().length > 0) {
                    for (Integer status : condition.getStatus()) {
                        statement.setInt(++index, status);
                    }
                }
            }
            
            ResultSet resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                count = resultSet.getInt("count");
            }
            
            return count;
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[InformationDao_JDBCImpl#countByCnd] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[InformationDao_JDBCImpl#countByCnd] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }
    
    @Override
    public List<TenderInformation> searchByCnd(TenderCondition condition) {
        if (logger.isInfoEnabled()) {
            logger.info("[InformationDao_JDBCImpl#searchByCnd] start.");
        }

        PreparedStatement statement = null;
        
        try {
            
            StringBuilder sql = new StringBuilder()
                .append("select \n")
                .append("  tender_id, \n")
                .append("  tender_no, \n")
                .append("  status, \n")
                .append("  status_name, \n")
                .append("  tender_title, \n")
                .append("  enroll_start_date, \n")
                .append("  enroll_end_date, \n")
                .append("  tender_end_date, \n")
                .append("  publish_result_date, \n")
                .append("  content_description, \n")
                .append("  vendor_requirement, \n")
                .append("  tender_pattern, \n")
                .append("  tender_pattern_name, \n")
                .append("  tender_template, \n")
                .append("  contact_name, \n")
                .append("  contact_tel, \n")
                .append("  enroll_count, \n")
                .append("  finalist_count, \n")
                .append("  quote_count, \n")
                .append("  create_user, \n")
                .append("  create_date, \n")
                .append("  update_user, \n")
                .append("  update_date, \n")
                .append("  delete_flag \n")
                .append("from \n")
                .append("  v_tender_information \n")
                .append("where \n")
                .append("  delete_flag = false \n");
            
            if (condition != null) {
                if (StringUtils.isNotEmpty(condition.getTenderTitle())) {
                    sql.append("and \n");
                    sql.append("  tender_title like ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollStartDateFrom())) {
                    sql.append("and \n");
                    sql.append("  enroll_start_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollStartDateTo())) {
                    sql.append("and \n");
                    sql.append("  enroll_start_date <= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollEndDateFrom())) {
                    sql.append("and \n");
                    sql.append("  enroll_end_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollEndDateTo())) {
                    sql.append("and \n");
                    sql.append("  enroll_end_date <= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getTenderEndDateFrom())) {
                    sql.append("and \n");
                    sql.append("  tender_end_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getTenderEndDateTo())) {
                    sql.append("and \n");
                    sql.append("  tender_end_date <= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getPublishResultDateFrom())) {
                    sql.append("and \n");
                    sql.append("  publish_result_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getPublishResultDateTo())) {
                    sql.append("and \n");
                    sql.append("  publish_result_date <= ? \n");
                }
                if (condition.getTenderPattern() != null && condition.getTenderPattern().intValue() > 0) {
                    sql.append("and \n");
                    sql.append("  tender_pattern = ? \n");
                }
                if (condition.getStatus() != null && condition.getStatus().length > 0) {
                    sql.append("and \n");
                    String strStatus = "";
                    for (int i = 0; i < condition.getStatus().length; i++) {
                        if (i == 0) {
                            strStatus = "?";
                        } else {
                            strStatus += ", ?";
                        }
                    }
                    sql.append("  status in (" + strStatus + ") \n");
                }
                if (condition.getTimeLimit() != null && condition.getTimeLimit()==0) {
                    sql.append("and \n");
                    sql.append("  create_date >= DATE_FORMAT(NOW(),'%Y') \n");
                }
                if (condition.getTimeLimit() != null && condition.getTimeLimit()==1) {
                    sql.append("and \n");
                    sql.append("  create_date >= DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'), INTERVAL 3 MONTH) \n");
                }
                if (condition.getTimeLimit() != null && condition.getTimeLimit()==2) {
                    sql.append("and \n");
                    sql.append("  create_date < DATE_FORMAT(NOW(),'%Y') \n");
                }
            }
            
            sql.append("order by \n");
            sql.append("  tender_id desc \n");
            
            if (condition != null) {
                if (condition.getPage() != null && condition.getRows() != null && condition.getPage() > 0 && condition.getRows() > 0) {
                    sql.append("limit ?, ? \n");
                }
            }
            
            if (logger.isTraceEnabled()) {
                logger.trace("[InformationDao_JDBCImpl#searchByCnd] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            
            if (condition != null) {
                int index = 0;
                if (StringUtils.isNotEmpty(condition.getTenderTitle())) {
                    statement.setString(++index, "%" + condition.getTenderTitle() + "%");
                }
                if (StringUtils.isNotEmpty(condition.getEnrollStartDateFrom())) {
                    statement.setString(++index, condition.getEnrollStartDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getEnrollStartDateTo())) {
                    statement.setString(++index, condition.getEnrollStartDateTo());
                }
                if (StringUtils.isNotEmpty(condition.getEnrollEndDateFrom())) {
                    statement.setString(++index, condition.getEnrollEndDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getEnrollEndDateTo())) {
                    statement.setString(++index, condition.getEnrollEndDateTo());
                }
                if (StringUtils.isNotEmpty(condition.getTenderEndDateFrom())) {
                    statement.setString(++index, condition.getTenderEndDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getTenderEndDateTo())) {
                    statement.setString(++index, condition.getTenderEndDateTo());
                }
                if (StringUtils.isNotEmpty(condition.getPublishResultDateFrom())) {
                    statement.setString(++index, condition.getPublishResultDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getPublishResultDateTo())) {
                    statement.setString(++index, condition.getPublishResultDateTo());
                }
                if (condition.getTenderPattern() != null && condition.getTenderPattern().intValue() > 0) {
                    statement.setInt(++index, condition.getTenderPattern());
                }
                if (condition.getStatus() != null && condition.getStatus().length > 0) {
                    for (Integer status : condition.getStatus()) {
                        statement.setInt(++index, status);
                    }
                }
                if (condition.getPage() != null && condition.getRows() != null && condition.getPage() > 0 && condition.getRows() > 0) {
                    statement.setInt(++index, (condition.getPage() - 1) * condition.getRows());
                    statement.setInt(++index, condition.getRows());
                }
            }
            
            ResultSet resultSet = statement.executeQuery();
            ResultSetMapper<TenderInformation> resultSetMapper = new ResultSetMapper<TenderInformation>();
            List<TenderInformation> TenderInformationList = resultSetMapper.mapResultSetToList(resultSet, TenderInformation.class);
            
            return TenderInformationList;
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[InformationDao_JDBCImpl#searchByCnd] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[InformationDao_JDBCImpl#searchByCnd] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }
}
